package com.buer.train.common.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;

public class ExcelUtil {

    public static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);


    /**
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static byte[] createExcelTemplate(String[] handers, List<String> categoryStr, List<String> supplier) {

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄

        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);
        style.setFont(fontStyle);

        //表头样式必填
        HSSFCellStyle styleRequre = wb.createCellStyle();
        styleRequre.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyleRequre = wb.createFont();
        fontStyleRequre.setFontName("微软雅黑");
        fontStyleRequre.setFontHeightInPoints((short) 12);
        fontStyleRequre.setBold(true);
        fontStyleRequre.setColor(Font.COLOR_RED);
        styleRequre.setFont(fontStyleRequre);


        //新建sheet
        HSSFSheet sheet1 = wb.createSheet("批量新增模板");

        //生成sheet1内容
        HSSFRow rowFirst = sheet1.createRow(0);//第一个sheet的第一行为标题
        //写标题
        for (int i = 0; i < handers.length; i++) {
            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            sheet1.setColumnWidth(i, 5000); //设置每列的列宽
            if(handers[i].startsWith("*")){
                cell.setCellStyle(styleRequre); //加样式
            }else {
                cell.setCellStyle(style); //加样式
            }

            cell.setCellValue(handers[i]); //往单元格里写数据
        }
       // String[] str1 = {"顶", "套", "副", "根", "双", "具", "个", "把", "件", "条", "米", "台", "组", "盘", "快", "罐", "箱", "升", "千克", "吨", "门", "支", "辆", "架", "艘"};


       // addValidationToSheet(wb, sheet1, str1, 'F', 1, 20000);
        addValidationToSheet(wb, sheet1, categoryStr.toArray(), 'B', 1, 20000);
        //addValidationToSheet(wb, sheet1, dataProdCate, 'B', 'C', 1, 20000);
        addValidationToSheet(wb, sheet1, supplier.toArray(), 'H', 1, 20000);
        //addValidationToSheet(wb, sheet1, dataArea, 'I', 'J', 1, 20000);

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            wb.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        byte[] bytes = bos.toByteArray();

        return bytes;


       /* try {

            File f = new File(filePath); //写文件

            //不存在则新增
            if (!f.getParentFile().exists()) {
                f.getParentFile().mkdirs();
            }
            if (!f.exists()) {
                f.createNewFile();
            }

            FileOutputStream out = new FileOutputStream(f);
            out.flush();

            wb.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }*/
    }


    private static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }


    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = column + "_parent";

        int rowIndex = 0;
        for (Object option : options) {
            int columnIndex = 0;
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(option.toString());
        }

        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);

        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<>();

        int rowIndex = 0;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            firstLevelItems.add(parent);
            List<String> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell = null;

            for (String child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child);
            }

            //char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            final String lastChildrenColumn = getcellColumnFlag(children.size() + 1);

            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

            //createName(workbook, parent,firstLevelItems.size(),rowIndex);
            DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
        }

        addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);

    }

    /**
     * 不可数字开头
     *
     * @param name
     * @return
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    private static Name createName(Workbook workbook, String nameName, int num,int i) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        //name.setRefersToFormula(formula);
        name.setRefersToFormula(nameName + "!$B$" + (i + 2) + ":$"
                + getcellColumnFlag(num) + "$" + (i + 2));
        return name;
    }

    // 根据数据值确定单元格位置（比如：28-AB）
    private static String getcellColumnFlag(int num) {
        String columFiled = "";
        int chuNum = 0;
        int yuNum = 0;
        if (num >= 1 && num <= 26) {
            columFiled = doHandle(num);
        } else {
            chuNum = num / 26;
            yuNum = num % 26;

            columFiled += doHandle(chuNum);
            columFiled += doHandle(yuNum);
        }
        return columFiled;
    }


    private static String doHandle(int num) {
        String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
                "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
                "W", "X", "Y", "Z" };
        return charArr[num - 1].toString();
    }

    public static void main(String[] args) {

        String fileName = "消防大队"; //模板名称
        String[] handers = {"序号", "*装备大类", "*装备细类", "装备名称", "数量", "单位", "采购单价", "规格型号", "生产厂商"}; //列标题
      /*  Map<String,String[]> map=new HashMap<>();
        String[] mhl = {"供水消防车（GS）","水罐消防车（SG）","泡沫消防车（PM）"};
        map.put("灭火类消防车",mhl);
        String[] jgl = {"登高平台消防车（DG）","云梯消防车（YT）","云梯消防车（YT）"};
        map.put("举高类消防车",jgl);*/

        Map<String, List<String>> data = new HashMap<>();
        data.put("灭火类消防车", Arrays.asList("供水消防车（GS）", "水罐消防车（SG）", "泡沫消防车（PM）"));
        data.put("举高类消防车", Arrays.asList("登高平台消防车（DG）", "云梯消防车（YT）", "云梯消防车（YT）"));


        //下拉框数据
        //List<String[]> downData = new ArrayList();
        //String[] str1 = {"男","女","未知"};
        // String[] str2 = {"北京","上海","广州","深圳","武汉","长沙","湘潭"};
       // String[] str1 = {"顶", "套", "副", "根", "双", "具", "个", "把", "件", "条", "米", "台", "组", "盘", "快", "罐", "箱", "升", "千克", "吨", "门", "支", "辆", "架", "艘"};
        //String[] str2 = {"国产", "进口"};
        // String[] str3 = {"01-汉族","02-蒙古族","03-回族","04-藏族","05-维吾尔族","06-苗族","07-彝族","08-壮族","09-布依族","10-朝鲜族","11-满族","12-侗族","13-瑶族","14-白族","15-土家族","16-哈尼族","17-哈萨克族","18-傣族","19-黎族","20-傈僳族","21-佤族","22-畲族","23-高山族","24-拉祜族","25-水族","26-东乡族","27-纳西族","28-景颇族","29-柯尔克孜族","30-土族","31-达斡尔族","32-仫佬族","33-羌族","34-布朗族","35-撒拉族","36-毛难族","37-仡佬族","38-锡伯族","39-阿昌族","40-普米族","41-塔吉克族","42-怒族","43-乌孜别克族","44-俄罗斯族","45-鄂温克族","46-德昂族","47-保安族","48-裕固族","49-京族","50-塔塔尔族","51-独龙族","52-鄂伦春族","53-赫哲族","54-门巴族","55-珞巴族","56-基诺族","98-外国血统","99-其他"};
        //downData.add(str1);
        //downData.add(str2);

        //String[] downRows = {"1", "2", "5", "12"}; //下拉的列序号数组(序号从0开始)

        //String path = "D:/common/aa.xlsx";
        //createExcelTemplate(path, handers, data);

        System.out.println((char)5);
    }
}
